import pymysql
import pandas as pd

# 读取文件
df=pd.read_csv("dataset.csv")
print(df.head())

# 连接数据库
connection = pymysql.connect(
    host='localhost',  # 仓库地址
    user='root',       # 仓库管理员
    password='1234',   # 管理员密码
    database='hqyj',   # 指定仓库
    port=3306,         # 仓库门牌号
    charset='utf8'     # 沟通语言
)

cursor = connection.cursor()

# 创建数据表
cursor.execute(
"""
CREATE TABLE IF NOT EXISTS pandas_data (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    age INT,
    nationality VARCHAR(50),
    education VARCHAR(20),
    gender VARCHAR(10)
);
"""
)
# 循环遍历df,然后insert插入数据表中
sql="""
insert into pandas_data(name,age,nationality,education,gender)
values(%s,%s,%s,%s,%s);
"""
for _,row in df.iterrows():
    params=(row['name'],row['age'],row['nationality'],row['education'],row['gender'])
    cursor.execute(sql,params)

# 提交
connection.commit()
print("数据插入完毕")

# 关闭
cursor.close()
connection.close()















